﻿using sMES_Base;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using sMES_Base.service;
using System.Data;
using load_on_machine_acc_info_get.Models;
using load_on_machine_acc_info_get_C.ORM;

namespace load_on_machine_acc_info_get
{
    /// <summary>
    /// [模製具模組][設備已用模具]
    /// Creater : JingHuang
    /// Creat time : 20181127
    /// 0052776: [2.1.0][load_on_machine_acc_info_get]服務重構
    /// </summary>
    public class load_on_machine_acc_info_get_utility : BaseUtility
    {
        public load_on_machine_acc_info_get_utility(ref BaseService CurrentService) : base(ref CurrentService)
        {
        }

        public DataTable unload_acc_from_machine_info_detail(REQ_load_on_machine_acc_info_get conten)
        {
            DataTable OutDataTable = new DataTable();

            List<ORM_Eqpequipment> _ORM_Eqpequipment = new List<ORM_Eqpequipment>();
            StringBuilder _sql = new StringBuilder();
            _sql.Append(" SELECT b.EQUIPMENTTYPE, b.EQUIPMENTNO ");
            _sql.Append("   FROM tblSMDAreaRelation a ");
            _sql.Append("   JOIN TBLEQPEQUIPMENTBASIS b ON a.OBJECTNO = b.EQUIPMENTNO ");
            _sql.Append("  WHERE OBJECTNO = '" + conten.EQUIPMENTNO + "' ");
            _sql.Append("    AND OBJECTAREATYPE = 1 ");


            ORM_Eqpequipment _equipment = CurrentService.GetDao().Query1Record<ORM_Eqpequipment>(_sql.ToString());
            //if (_equipment == null)
            //    throw new ServiceException(ServiceCode.DataNotFoundException, "[%Module_ACC.Equipemnt%]"+ conten.EQUIPMENTNO+ "[%Module_ACC.Is_not_exist%]");

            _sql = new StringBuilder();
            _sql.Append(" SELECT A.AccessoryNo AS \"AccessoryNo\", ");
            _sql.Append("        A.AccessoryType AS \"AccessoryType\", ");
            _sql.Append("        D.AccessoryCategory AS \"AccessoryCategory\", ");
            _sql.Append("        B.AccessoryVersion AS \"AccessoryVersion\", ");
            _sql.Append("        B.LocatorNo AS \"LocatorNo\", ");
            //20190129 modify by Dustdusk for M#: 把計算從程式改成SQL
            _sql.Append("        CASE WHEN C.LifeManage is not null AND C.LifeManage != 0 AND C.ScrapType = 1 THEN C.RepairCycle - AccumulateQTY ");
            _sql.Append("             ELSE NULL END AS \"ResidualLife\", ");
            _sql.Append("        CASE WHEN C.LifeManage is not null AND C.LifeManage != 0 AND C.ScrapType = 1 THEN C.ScrapLife - ACCTotalUsedQTY ");
            _sql.Append("             ELSE NULL END AS \"TotalResidualLife\", ");
            _sql.Append("        B.ACCTotalUsedQTY AS \"ACCTotalUsedQTY\", ");
            _sql.Append("        B.AccumulateQTY AS \"AccumulateQTY\", ");
            _sql.Append("        C.LifeManage AS \"LifeManage\", ");
            _sql.Append("        C.ScrapType AS \"ScrapType\", ");
            _sql.Append("        C.RepairCycle AS \"RepairCycle\", ");
            _sql.Append("        D.AccessoryName AS \"AccessoryName\", ");
            // 20210426 Modify by Zeke for 85272: 模具上模報錯：TBLEMSEQPACCSTATE重覆索引 [金名山]
            // 20220208 Modify by 翰鴻 for M#102969
            _sql.Append("        B.ACCSerialNo \"ACCSerialNo\", ");
            _sql.Append("        B.TempLocatorNo \"TempLocatorNo\", ");
            //20220308 add by 翰鴻 for M#102577            
	        _sql.Append("       B.AcLoadTime \"AcLoadTime\",      ");
	        _sql.Append("       B.AcUnLoadTime \"AcUnLoadTime\",   ");
	        _sql.Append("       F.LoadTools \"LoadTools\",       ");
	        _sql.Append("       F.UnloadTools \"UnloadTools\",     ");
	        _sql.Append("       F.LoadTime \"LoadTime\",       ");
            _sql.Append("       F.UnloadTime \"UnloadTime\"      ");
            _sql.Append(" FROM TBLEMSEQPACCSTATE A ");
            _sql.Append("   LEFT JOIN tblEMSAccessoryState B ");
            _sql.Append("          ON A.AccessoryNo = B.AccessoryNo ");
            _sql.Append("         AND A.AccessoryType = B.AccessoryType ");
            _sql.Append("   LEFT JOIN tblEQPAccessoryLife C ON A.AccessoryType = C.Accessory ");
            _sql.Append("   LEFT JOIN TBLEQPACCESSORYBASIS D ");
            _sql.Append("          ON A.AccessoryNo = D.AccessoryNo ");
            _sql.Append("         AND A.AccessoryVersion = D.AccessoryVersion ");
            _sql.Append("         AND A.AccessoryType = D.AccessoryType ");
            //20220308 add by 翰鴻 for M#102577
            _sql.Append("LEFT JOIN tblACCChangeTime F ON A.AccessoryNo = F.AccessoryNo AND A.AccessoryVersion = F.AccessoryVersion ");
            _sql.Append(" WHERE 1 = 1 ");
            if (_equipment != null)
            {
                _sql.Append("   And A.EquipmentNo = '" + _equipment.EQUIPMENTNO + "' ");
                _sql.Append("   And A.EquipmentType = '" + _equipment.EQUIPMENTTYPE + "' ");
            }
            else {
                _sql.Append("   And A.EquipmentNo = '" + conten.EQUIPMENTNO + "' ");
            }

            OutDataTable = CurrentService.GetDao().Query(_sql.ToString());
            OutDataTable.Columns.Add("CurrentMoldUps");

            //當沒有資料的時候需要給一筆空的 row 給H5
            if (OutDataTable.Rows.Count.Equals(0))
            {
                DataRow Insert_row = OutDataTable.NewRow();
                OutDataTable.Rows.Add(Insert_row);
            }
            else
            {
                // 查询上模次数
                _sql.Clear();
                _sql.Append(" select AccessoryNo,AccessoryVersion,CurrentMoldUps from TBLCUSMoldMoves ");
                List<ORM_TBLCUSMoldMoves> listCusMoldMoves = CurrentService.GetDao().Query<ORM_TBLCUSMoldMoves>(_sql.ToString());

                foreach (DataRow dr in OutDataTable.Rows)
                {
                    dr["CurrentMoldUps"] = "0";

                    var _cusMoves = listCusMoldMoves.Where(_ => _.AccessoryNo == dr["AccessoryNo"].ToString() && _.AccessoryVersion == dr["AccessoryVersion"].ToString());
                    if (_cusMoves != null)
                        if (_cusMoves.Count() > 0)
                            dr["CurrentMoldUps"] = _cusMoves.First().CurrentMoldUps.ToString();
                }
            }

            return OutDataTable;
        }
    }
}
